"""
@author: 周末
--------------------------------------
@project: application
--------------------------------------
@contact:15892766524@163.com
--------------------------------------
@file: Files.py
--------------------------------------
@time: 2021/11/12 12:15
--------------------------------------
"""
import pandas as pd
import os

from .Db import Db


class Files(object):
    def __init__(self, file):
        """
        初始化
        :param file: 文件名称
        """
        self.__file_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), f'Data/{file}')
        self.file_suffix = os.path.splitext(self.__file_path)[1]
        self.table = None
        if self.file_suffix not in ('.csv', '.xls'):
            print('文件格式不正确')
            return

    def write(self, data):
        """
        文件写操作
        :param data: 数据
        :return:
        """
        # 数据初始化成为DataFrame对象
        df = pd.DataFrame(data)
        # 将数据写入
        df.to_csv(self.__file_path, index=False)

    def read(self, type='list'):
        """
        读取文件数据
        :param type: 读取文件类型 list dict
        :return:
        """
        if self.file_suffix == '.csv':
            self.table = pd.read_csv(self.__file_path, keep_default_na=False)
        elif self.file_suffix == '.xls':
            self.table = pd.read_excel(self.__file_path, keep_default_na=False)
        if self.table is not None:
            if type == 'list':
                return self.get_data_list()
            else:
                return self.get_data_dict()

    def get_data_list(self):
        """读写表格数据 以列表形式返回"""
        if self.table is not None:
            return self.table.values.tolist()

    def get_data_dict(self):
        """读取表格数据 以字典形式返回"""
        if self.table is not None:
            return [self.table.loc[i].to_dict() for i in self.table.index.values]


if __name__ == '__main__':
    db = Db(host='139.159.146.104', user='root', password='itsource.cn..', port=3306, db='03_mall',
            charset='utf8')
    # sql = "select * from ums_member where username = %s"
    sql = 'select username,password from ums_member where username like "%Tomcat%"'
    # print(db.fetchOne(sql, ('Tomcat_16890000584',)))
    data = db.fetchall(sql)
    usernames = []
    passwords = []
    for i in data:
        usernames.append(i['username'])
        passwords.append(i['password'])
    data = {"username": usernames, "password": passwords}
    file = Files('members.csv')
    file.write(data=data)
    print(file.read(type='dict'))